Arbeitspaket (AP) 3: Management & Nutzung Räumliche Daten¶
Angaben Studierende(r) (fehlende Angaben ergänzen)¶
| Vorname: | Melisa |
| Nachname: | Aydemir |
| Immatrikulationsnummer: | 24672255 |
| Modul: | Data Science |
| Prüfungsdatum / Raum / Zeit: | 07.10.2024 / Raum: SF O3.54 / 8:00 – 11:45 |
| Erlaubte Hilfsmittel: | w.MA.XX.DS.24HS (Data Science) Open Book, Eigener Computer, Internet-Zugang |
| Nicht erlaubt: | Nicht erlaubt ist der Einsatz beliebiger Formen von generativer KI (z.B. Copilot, ChatGPT) sowie beliebige Formen von Kommunikation oder Kollaboration mit anderen Menschen. |
Bewertungskriterien¶
(max. erreichbare Punkte: 48)¶
| Kategorie | Beschreibung | Punkteverteilung |
|---|---|---|
| Code nicht lauffähig oder Ergebnisse nicht sinnvoll | Der Code enthält Fehler, die verhindern, dass er ausgeführt werden kann (z.B. Syntaxfehler) oder es werden Ergebnisse ausgegeben, welche nicht zur Fragestellung passen. | 0 Punkte |
| Code lauffähig, aber mit gravierenden Mängeln | Der Code läuft, aber die Ergebnisse sind aufgrund wesentlicher Fehler unvollständig (z.B. fehlende Joins, gravierende Fehler in SQL-Abfragen). Nur geringer Fortschritt erkennbar. | 25% der max. erreichbaren Punkte |
| Code lauffähig, aber mit mittleren Mängeln | Der Code läuft und liefert teilweise korrekte Ergebnisse, aber es gibt grössere Fehler (z.B. fehlende Spalten, unvollständige SQL-Abfragen). Die Ergebnisse sind nachvollziehbar, aber unvollständig oder ungenau. | 50% der max. erreichbaren Punkte |
| Code lauffähig, aber mit minimalen Mängeln | Der Code läuft und liefert ein weitgehend korrektes Ergebnis, aber kleinere Fehler (z.B. falsche oder fehlende Sortierung, Rundung von Werten falsch) beeinträchtigen die Vollständigkeit des Ergebnisses. | 75% der max. erreichbaren Punkte |
| Code lauffähig und korrekt | Der Code läuft einwandfrei und liefert das korrekte Ergebnis ohne Mängel. | 100% der max. erreichbaren Punkte |
Python Libraries und Settings¶
In [1]:
# Libraries
import os
import folium
import pandas as pd
import geopandas as gpd
from sqlalchemy import create_engine, text
# Ignore warnings
import warnings
warnings.filterwarnings("ignore")
print(os.getcwd())
/workspaces/python_postgresql_postgis
Vorbereitung (Hinweis: dieser Teil wird nicht bewertet)¶
1.) Starten Sie eine GitHub Codespaces Instanz auf Basis Ihres Forks des folgenden GitHub Repositories:¶
GitHub-Repository: https://github.com/mario-gellrich-zhaw/python_postgresql_postgis¶
WICHTIG!!! Verwenden Sie eine GitHub Codespaces Instanz mit ausreichend Arbeitsspeicher (4core, 16GB RAM).¶
Hinweis:
- Im Unterricht wurden bereits sämtliche Installationen und Einstellungen inkl. der Registrierung des Datenbank Servers auf pgAdmin vorgenommen.
- Falls Sie die Codespaces-Instanz neu erstellen müssen, folgen Sie bitte den detaillierten Erklärungen auf der README-Seite des GitHub Repositories.
2.) Erstellen und Testen Sie die Datenbankverbindung mit der 'osm_switzerland' Datenbank.¶
In [2]:
# Set up Database Connection
user = "pgadmin"
password = "geheim"
host = "localhost"
port = "5432"
database = "osm_switzerland"
# Erstellen der Connection URL
db_connection_url = "postgresql://" + user + ":" + password +\
"@" + host + ":" + port + "/" + database
# Erstellen SQLAlchemy Engine
engine = create_engine(db_connection_url)
# Test der Connection
with engine.connect() as connection:
result = connection.execute(text('SELECT current_database()'))
print(result.fetchone())
# Verbindung trennen
engine.dispose()
('osm_switzerland',)
Aufgaben (Dieser Teil wird bewertet!)¶
Hinweise zu den folgenden Aufgabenstellungen:
- In diesem Jupyter Notebook gibt es jeweils zwei Code-Zellen pro Aufgabe:
- Eine Codezelle mit Python-Code und einem SQL-Statement für die Datenbank-Abfrage.
- Eine Codezelle mit Python-Code für die Kartendarstellung der Ergebnisse der jeweiligen SQL-Abfrage.
- In den Codezellen für die Datenbank-Abfrage muss jeweils das SQL-Statement ergänzt werden.
- In den Codezellen für die Kartendarstellung muss nur dann der Python Code ergänzt werden, wenn in der Aufgabe danach gefragt wird.
Aufgabe (1): Erstellen Sie eine Abfrage sämtlicher Autoreparatur-Werkstätten in der Schweiz¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Daten in der Tabelle 'planet_osm_point'.
- Stellen sie in der Ergebnistabelle die Spalten: osm_id, shop sowie die transformierte Geometrie als Spalte geom dar.
- Tipp: Die Geometry wird mit Hilfe der Funktion st_transform() transformiert, z.B.: st_transform(p.way, 4326) AS geom.
- Tipp: Autoreparatur-Werkstätten sind mit dem key:value Paar shop='car_repair' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 6)
In [3]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
h.osm_id,
h.shop,
h.name,
ST_Transform(h.way, 4326) AS geom
FROM planet_osm_point h
WHERE h.shop = 'car_repair';"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[3]:
| osm_id | shop | name | geom | |
|---|---|---|---|---|
| 0 | 1811755810 | car_repair | Grenzgarage | POINT (9.62898 47.45412) |
| 1 | 9408250312 | car_repair | Gebr. Wirth AG | POINT (9.63098 47.45327) |
| 2 | 2539306181 | car_repair | Lantech | POINT (9.58444 47.46663) |
| 3 | 2530851973 | car_repair | Garage Welpe | POINT (9.58777 47.46975) |
| 4 | 3346119599 | car_repair | Garage Martino GmbH | POINT (9.49096 47.47737) |
| ... | ... | ... | ... | ... |
| 1461 | 2907856551 | car_repair | Sportgarage R. Mühlemann | POINT (9.13433 47.62214) |
| 1462 | 3358843432 | car_repair | Auto Lüthi | POINT (9.16914 47.58401) |
| 1463 | 4386729493 | car_repair | Garage Stahel | POINT (9.26639 47.55844) |
| 1464 | 6092408245 | car_repair | Nussberger Direktimport | POINT (9.34644 47.53412) |
| 1465 | 1485836431 | car_repair | Toyota Garage Schlauri AG | POINT (9.16553 47.64061) |
1466 rows × 4 columns
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [4]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=8,
tiles='CartoDB positron')
# Map settings
folium.GeoJson(
gdf,
name='map'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[4]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (2) Erstellen Sie eine Abfrage aller Biergärten in der Schweiz.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Daten in den Tabellen 'planet_osm_point'.
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, amenity, name und die transformierte Geometrie als Spalte geom dar.
- Tipp: Biergärten sind mit dem key:value Paar amenity='biergarten' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 6)
In [5]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
h.osm_id,
h.amenity,
h.name,
ST_Transform(h.way, 4326) AS geom
FROM planet_osm_point h
WHERE h.amenity = 'biergarten'
;"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf.head()
Out[5]:
| osm_id | amenity | name | geom | |
|---|---|---|---|---|
| 0 | 704467869 | biergarten | Bierhalle | POINT (9.6068 47.40694) |
| 1 | 423833242 | biergarten | Brasserie-Bar de la Poste | POINT (6.93199 46.99151) |
| 2 | 309162302 | biergarten | Pointe du Grain | POINT (6.83709 46.92606) |
| 3 | 6741052485 | biergarten | None | POINT (6.10754 46.16777) |
| 4 | 746772927 | biergarten | Buvette de l'alpage du col du Lein | POINT (7.15997 46.11045) |
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [6]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=8,
tiles='CartoDB positron')
# Map settings
folium.GeoJson(
gdf,
name='map'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[6]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (3): Erstellen Sie eine Abfrage aller Gebäude in der Stadthausstrasse in Winterthur, welche vollständige Adressangaben besitzen.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Daten in der Tabelle 'planet_osm_polygon'.
- Vollständige Adressangabe bedeutet: Strassenname, Haunummer, PLZ, Gemeindename sind vorhanden.
- Stellen Sie in der Ergebnistabelle sämtliche Adressangaben sowie die transformierte Geometrie als Spalte geom dar.
- Verwenden Sie für die Darstellung als Hintergrundkarte ein Satellitenbild (ESRIWorldImagery) als maptile.
- Tipp: Gebäude sind in der Spalte 'building' klassifiziert. Mit WHERE building IS NOT NULL können Sie Gebäude filtern.
(max. erreichbare Punkte: 6)
In [7]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
p.osm_id,
p."addr:street",
p."addr:housenumber",
p."addr:city",
p."addr:postcode",
p.building,
st_transform(p.way, 4326) AS geom
FROM
public.planet_osm_polygon AS p
WHERE
p.building IS NOT NULL
AND p."addr:street" IN ('Stadthausstrasse')
AND p."addr:housenumber" IS NOT NULL
AND p."addr:city" IS NOT NULL
AND p."addr:postcode" IN ('8400')
;"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[7]:
| osm_id | addr:street | addr:housenumber | addr:city | addr:postcode | building | geom | |
|---|---|---|---|---|---|---|---|
| 0 | 94283231 | Stadthausstrasse | 145 | Winterthur | 8400 | apartments | POLYGON ((8.72414 47.4995, 8.72417 47.49939, 8... |
| 1 | 94283304 | Stadthausstrasse | 143 | Winterthur | 8400 | office | POLYGON ((8.72427 47.49952, 8.72431 47.4994, 8... |
| 2 | 24804763 | Stadthausstrasse | 22 | Winterthur | 8400 | office | POLYGON ((8.72448 47.49982, 8.72453 47.4997, 8... |
| 3 | 26992511 | Stadthausstrasse | 24 | Winterthur | 8400 | retail | POLYGON ((8.72405 47.49972, 8.72407 47.49967, ... |
| 4 | 134980581 | Stadthausstrasse | 10b | Winterthur | 8400 | yes | POLYGON ((8.72652 47.50075, 8.72661 47.50063, ... |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 56 | 232998352 | Stadthausstrasse | 133 | Winterthur | 8400 | apartments | POLYGON ((8.72543 47.4995, 8.72546 47.49949, 8... |
| 57 | 188158693 | Stadthausstrasse | 119 | Winterthur | 8400 | yes | POLYGON ((8.72634 47.49979, 8.7264 47.49961, 8... |
| 58 | 188158697 | Stadthausstrasse | 117 | Winterthur | 8400 | apartments | POLYGON ((8.72682 47.49986, 8.72687 47.49972, ... |
| 59 | 188158696 | Stadthausstrasse | 115 | Winterthur | 8400 | commercial | POLYGON ((8.72694 47.49988, 8.72696 47.49974, ... |
| 60 | 188158695 | Stadthausstrasse | 113 | Winterthur | 8400 | apartments | POLYGON ((8.72702 47.4999, 8.72706 47.49975, 8... |
61 rows × 7 columns
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [8]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=17,
tiles='ESRIWorldImagery')
# Map settings
folium.Choropleth(
geo_data=gdf,
name='map',
fill_color='greenyellow'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[8]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (4): Erstellen Sie eine Abfrage aller Strassen in der Schweiz, welche als 'motorway' klassifiziert sind.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Informationen in der Tabelle 'planet_osm_roads'.
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, highway und die transformierte Geometrie als Spalte geom dar.
- Tipp: Motorways sind mit dem key:value Paar highway='motorway' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 6)
In [9]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
p.osm_id,
p.highway,
ST_TRANSFORM(p.way, 4326) AS geom
FROM public.planet_osm_roads AS p
WHERE
highway = 'motorway'
;"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[9]:
| osm_id | highway | geom | |
|---|---|---|---|
| 0 | 1236416666 | motorway | LINESTRING (9.64218 47.43433, 9.6422 47.43402,... |
| 1 | 552469430 | motorway | LINESTRING (9.64232 47.43412, 9.6423 47.43469) |
| 2 | 552469432 | motorway | LINESTRING (9.6423 47.43469, 9.64232 47.43504,... |
| 3 | 552469428 | motorway | LINESTRING (9.64312 47.43793, 9.6429 47.43753,... |
| 4 | 186132194 | motorway | LINESTRING (9.64235 47.43533, 9.6424 47.43568,... |
| ... | ... | ... | ... |
| 8270 | 277760689 | motorway | LINESTRING (9.16091 47.6594, 9.16081 47.65903,... |
| 8271 | 277760690 | motorway | LINESTRING (9.16103 47.65931, 9.16112 47.65984... |
| 8272 | 27158313 | motorway | LINESTRING (9.16125 47.66169, 9.16124 47.66165... |
| 8273 | 318438636 | motorway | LINESTRING (9.16136 47.66118, 9.1614 47.66144) |
| 8274 | 63904479 | motorway | LINESTRING (9.1614 47.66144, 9.16145 47.66172,... |
8275 rows × 3 columns
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [10]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=9,
tiles='CartoDB positron')
# Map settings
folium.Choropleth(
geo_data=gdf,
name='map',
line_weight=3,
line_color='red'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[10]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (5): Erstellen Sie eine Abfrage aller Schweizer Flüsse. Generieren Sie zusätzlich Buffer um die Flüsse mit einer Breite von 2000m.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Informationen in der Tabelle 'planet_osm_line'.
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, waterway sowie die transformierte Geometrie als Spalte geom dar.
- Tipp: Flüsse sind mit dem key:value Paar waterway='river' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
- Tipp: Per Default wird für jedes Fluss-Segment ein separater Buffer erstellt. Es ist nicht notwendig daraus einen einzelnen Buffer zu generieren.
(max. erreichbare Punkte: 8)
In [12]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
1 as group_id,
p.osm_id,
p.waterway,
ST_TRANSFORM(ST_Buffer(p.way::geometry, 2000), 4326) AS geom
FROM public.planet_osm_line AS p
WHERE
waterway = 'river'
;"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[12]:
| group_id | osm_id | waterway | geom | |
|---|---|---|---|---|
| 0 | 1 | 235746880 | river | POLYGON ((7.56096 47.54614, 7.56119 47.54639, ... |
| 1 | 1 | 23075435 | river | POLYGON ((7.55867 47.54372, 7.55906 47.54469, ... |
| 2 | 1 | 235747560 | river | POLYGON ((7.55813 47.54244, 7.55823 47.54267, ... |
| 3 | 1 | 26611349 | river | POLYGON ((7.57018 47.55788, 7.57035 47.558, 7.... |
| 4 | 1 | 26611348 | river | POLYGON ((7.56643 47.55509, 7.56864 47.55694, ... |
| ... | ... | ... | ... | ... |
| 2554 | 1 | 1158179331 | river | POLYGON ((7.06413 47.42918, 7.06661 47.43087, ... |
| 2555 | 1 | 151506395 | river | POLYGON ((7.05284 47.43218, 7.05228 47.43358, ... |
| 2556 | 1 | 151506360 | river | POLYGON ((7.07528 47.42155, 7.0738 47.42181, 7... |
| 2557 | 1 | 836397586 | river | POLYGON ((7.10044 47.34499, 7.09983 47.34486, ... |
| 2558 | 1 | 688223429 | river | MULTIPOLYGON (((7.17816 47.5101, 7.17792 47.51... |
2559 rows × 4 columns
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [13]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=8,
tiles='CartoDB positron')
# Map settings
folium.Choropleth(
geo_data=gdf,
name='map',
fill_color='greenyellow'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[13]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (6): Erstellen Sie eine Abfrage der Bäckerei-Geschäfte in Zürich und Winterthur.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Daten in den Tabellen 'planet_osm_point' (Backereien).
- Verwenden Sie die Städtenamen aus den Adressangaben für die Abfrage der Bäckerei-Standorte (Zürich, Winterthur).
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, shop, name, "addr:city" sowie die transformierte Geometrie als geom dar.
- Wählen Sie eine Satelliten Karte von ESRI als Hintergrundkarte (maptile).
- Sortieren Sie die Bäckerei-Geschäfte aufsteigend nach osm_id.
- Tipp: Bäckerei-Geschäfte sind mit dem key:value Paar shop='bakery' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 8)
In [14]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
h.osm_id,
h.shop,
h.name,
h."addr:city",
ST_Transform(h.way, 4326) AS geom
FROM planet_osm_point h
WHERE shop = 'bakery'
AND h."addr:city" IN ('Zürich', 'Winterthur')
ORDER BY h.osm_id ASC
;"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[14]:
| osm_id | shop | name | addr:city | geom | |
|---|---|---|---|---|---|
| 0 | 252457457 | bakery | Steiner Flughafebeck | Zürich | POINT (8.49779 47.40311) |
| 1 | 266630770 | bakery | Brezelkönig | Zürich | POINT (8.48875 47.39147) |
| 2 | 267879346 | bakery | Walter Buchmann | Zürich | POINT (8.51892 47.36239) |
| 3 | 268602152 | bakery | Moser's | Zürich | POINT (8.54945 47.3632) |
| 4 | 270794699 | bakery | John Baker | Zürich | POINT (8.56645 47.36493) |
| ... | ... | ... | ... | ... | ... |
| 101 | 10082330824 | bakery | RAM3 | Winterthur | POINT (8.74193 47.49395) |
| 102 | 10884168277 | bakery | Juliette | Zürich | POINT (8.5328 47.36658) |
| 103 | 10946043353 | bakery | Babu's Bakery | Zürich | POINT (8.51568 47.37473) |
| 104 | 10955852823 | bakery | Täglich Brot | Zürich | POINT (8.50705 47.36079) |
| 105 | 11951988149 | bakery | Wagner | Zürich | POINT (8.51763 47.3698) |
106 rows × 5 columns
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [15]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=12,
tiles='EsriWorldImagery')
# Map settings
folium.GeoJson(
gdf,
name='map',
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[15]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (7): Erstellen Sie eine Abfrage sämtlicher Coiffeur-Geschäfte in einem Radius von 500m um den Hauptbahnhof in Zürich.¶
Details zur Aufgabenstellung:
- Sie finden die Daten in der Tabelle 'planet_osm_point'.
- Berechnen Sie in der Abfrage die Distanz jedes Coiffeur-Geschäfts zum Hauptbahnhof in Metern als Spalte 'distance_meters'.
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, shop, name, distance_meters, sowie die transformierte Geometrie als geom dar.
- Wählen Sie eine Satelliten Karte von ESRI als Hintergrundkarte (maptile).
- Integrieren Sie in die Kartendarstellung den Namen (Spalte 'name') der Coiffeur-Geschäfte als Popup.
- Tipp: Coiffeur-Geschäfte sind mit dem key:value Paar shop='hairdresser' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 8)
In [16]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
p.osm_id,
p.shop,
p.name,
ST_Distance(
ST_Transform(p.way, 4326)::geography,
-- Central station coordinates
ST_SetSRID(ST_MakePoint(8.53936, 47.3781), 4326)::geography
) AS distance_meters,
ST_TRANSFORM(p.way, 4326) AS geom
FROM
planet_osm_point AS p
WHERE
p.shop = 'hairdresser'
AND ST_DWithin(
ST_Transform(p.way, 4326)::geography,
-- Central station coordinates
ST_SetSRID(ST_MakePoint(8.53936, 47.3781), 4326)::geography,
500
)
;"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf.head()
Out[16]:
| osm_id | shop | name | distance_meters | geom | |
|---|---|---|---|---|---|
| 0 | 11758825735 | hairdresser | MSH Salon | 361.728635 | POINT (8.5424 47.38062) |
| 1 | 1357158512 | hairdresser | Coiffeur-Studio Silvia Baumgartner | 310.980273 | POINT (8.54214 47.38017) |
| 2 | 4833061593 | hairdresser | McCoiffure | 226.319141 | POINT (8.53695 47.37931) |
| 3 | 4424939218 | hairdresser | McCoiffure | 111.635316 | POINT (8.53791 47.37791) |
| 4 | 4244059289 | hairdresser | Art Coiffure Kaiser | 133.439602 | POINT (8.53788 47.37745) |
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [17]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=16,
tiles='ESRIWorldImagery')
# Map settings
folium.GeoJson(
gdf,
name='map',
popup=folium.GeoJsonPopup(fields=['name'])
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[17]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Jupyter notebook --footer info-- (please always provide this at the end of each notebook)¶
In [ ]:
import os
import platform
import socket
from platform import python_version
from datetime import datetime
print('-----------------------------------')
print(os.name.upper())
print(platform.system(), '|', platform.release())
print('Datetime:', datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
print('Python Version:', python_version())
print('IP Address:', socket.gethostbyname(socket.gethostname()))
print('-----------------------------------')